package com.cn.jsdr.common;

import java.util.*;

public class SqlConfig {
    public static final String KEY_WORD="keyword";//查询标识
    public static final String KEY_WORD_Q="select";//查询标识
    public static final String KEY_WORD_C="select";//存储过程标识
    public static final String KEY_WORD_A="insert";//插入标识
    public static final String KEY_WORD_U="update";//更新标识
    public static final String KEY_WORD_D="delete";//删除标识
    public static final String M_TABLE="mtable";  //要查询的表
    public static final String STATIC_COND="statecond";// 静态条件  不需要加工
    public static final String DYN_CONDS="dynconds";//动态条件  需要加工
    public static final String ENDING="ending";//结束字段
    public static final String JOINON="joinon";// 条件链接
    public static final String SORT="sort"; //排序  分组
    public static final String PAGING="paging"; //分页字段设置
    public static final String PAGE_SIZE="pagesize";// 分页中大小
    public static final String PAGE_INDEX="pageindex";//分页中  页数标识
    public static final String PRIFLAG="1";//主键标识
    public static final String NOTNULL="4";//非空标识
    public static final String NOTNULLNUM="6";//非空标识  必填数字
    public static final String NUMFLAG="2";//数字布尔标识
    public static final String BIGSPLIT="@";//分隔标识
    public static final String MINSPLIT="#";//最小分隔标识
    //011 3数字主键有数据库生成不需要   101 5必填主键   主键一定必填
    /**
     *
     * @param sqlslice
     * @param param   keyword  mtable  statecond  dynconds  ending
     * @return
     */
    public static String getDeletesql(Map<String,String> sqlslice, Map<String,Object> param){
        String keyword = sqlslice.get(KEY_WORD);//关键字  必须
        if(!keyword.equalsIgnoreCase(KEY_WORD_D)){
            throw new RuntimeException("keyword :关键字错误！"+keyword);
        }
        String mtable = sqlslice.get(M_TABLE);//表   必须   不加工
        if(mtable == null||"".equals(mtable)){
            throw new RuntimeException("mtable :要删除的表不能为空！"+mtable);
        }
        String statecond = sqlslice.get(STATIC_COND);//附加部分 不加工
        String dynconds = sqlslice.get(DYN_CONDS);//条件  过滤条件
        String ending = sqlslice.get(ENDING);//结束的部分
        StringBuffer sb = new StringBuffer();
        statecond=statecond==null?"":statecond;
        ending =ending==null?"":ending;
        dynconds = dynconds==null||dynconds==""?"":getCond(dynconds,param);
        sb.append(keyword).append(" ").append(mtable).append(" ")
                .append(statecond).append(" ").append(dynconds).append(" ").append(ending);
        return sb+"";
    }

    /**
     *
     * @param sqlslice
     * @param param  keyword  mtable  joinon statecond  dynconds  ending sort paging
     * @return
     */
    public static String getQuerySql(Map<String,String> sqlslice, Map<String,Object> param){
        // String rank = sqlslice.get("rank");//关键字  必须
        String keyword = sqlslice.get(KEY_WORD);//关键字  必须
        if(!keyword.equalsIgnoreCase(KEY_WORD_Q)){
            throw new RuntimeException("keyword :关键字错误！"+keyword);
        }
        String mtable = sqlslice.get(M_TABLE);//表   必须   不加工
        if(mtable == null||"".equals(mtable)){
            throw new RuntimeException("mtable :要查询的表不能为空！"+mtable);
        }
        String joinon = sqlslice.get(JOINON);// 增改字段  join on查询字段的条件
        String statecond = sqlslice.get(STATIC_COND);//附加部分 不加工
        String dynconds = sqlslice.get(DYN_CONDS);//条件  过滤条件
        String ending = sqlslice.get(ENDING);//结束的部分
        StringBuffer sb = new StringBuffer();
        statecond=statecond==null?"":statecond;
        ending =ending==null?"":ending;
        String sort = param.get(SORT)==null?"":param.get(SORT)+"";//排序
        //String sort = param.get("sort")==null?"":Config.getOrder(param.get("sort")+"",param);//排序
        String paging =sqlslice.get(PAGING);
        paging =paging==null?"":getPaging(paging,param); //分页
        dynconds = dynconds==null||dynconds==""?"":getCond(dynconds,param);
        joinon = joinon==null||joinon==""?"":getCond(joinon,param);
        sb.append(keyword).append(" ").append(mtable)
                .append(" ").append(joinon).append(" ").append(statecond).append(" ")
                .append(dynconds).append(" ").append(sort).append(" ").append(paging)
                .append(" ").append(ending);
        return sb.toString();
    }

    /**
     *
     * @param sqlslice
     * @param param  keyword  mtable  joinon statecond  dynconds  ending
     * @return
     */
    public static String getUpdateSql(Map<String,String> sqlslice, Map<String,Object> param) {
        String keyword = sqlslice.get(KEY_WORD);//关键字  必须
        if(!keyword.equalsIgnoreCase(KEY_WORD_U)){
            throw new RuntimeException("keyword :关键字错误！"+keyword);
        }
        String mtable = sqlslice.get(M_TABLE);//表   必须   不加工
        if(mtable == null||"".equals(mtable)){
            throw new RuntimeException("mtable :要更新的表不能为空！"+mtable);
        }
        String joinon = sqlslice.get(JOINON);// 增改字段  查询字段的条件
        String statecond = sqlslice.get(STATIC_COND);//附加部分 不加工
        String dynconds = sqlslice.get(DYN_CONDS);//条件  过滤条件
        String ending = sqlslice.get(ENDING);//结束的部分
        StringBuffer sb = new StringBuffer();
        statecond=statecond==null?"":statecond;
        ending =ending==null?"":ending;
        dynconds = dynconds==null||dynconds==""?"":getCond(dynconds,param);
        joinon = joinon==""?"":getCond(joinon,param);
        sb.append(keyword).append(" ").append(mtable).append(" ")
                .append(joinon).append(" ").append(statecond).append(" ")
                .append(dynconds).append(" ").append(ending);
        return sb+"";
    }

    /**
     *
     * @param sqlslice
     * @param param  keyword  mtable   dynconds  ending
     * @return
     */
    public static String getAddSql(Map<String,String> sqlslice, Map<String,Object> param) {
        String keyword = sqlslice.get(KEY_WORD);//关键字  必须
        if(!keyword.equalsIgnoreCase(KEY_WORD_A)){
            throw new RuntimeException("keyword :关键字错误！"+keyword);
        }
        String mtable = sqlslice.get(M_TABLE);//表   必须   不加工
        if(mtable == null||"".equals(mtable)){
            throw new RuntimeException("mtable :要更新的表不能为空！"+mtable);
        }
        String dynconds = sqlslice.get(DYN_CONDS);//条件  过滤条件
        String ending = sqlslice.get(ENDING);//结束的部分
        ending =ending==null?"":ending;
        StringBuffer sb = new StringBuffer();
        dynconds = dynconds==null||dynconds==""?"":getOneInsert(dynconds,param);
        sb.append(keyword).append(" ").append(mtable).append(" ")
                .append(dynconds).append(" ").append(ending);
        return sb+"";
    }
    public static String getAddSqls(Map<String,String> sqlslice, List<Map<String,Object>> param) {
        String keyword = sqlslice.get(KEY_WORD);//关键字  必须
        if(!keyword.equalsIgnoreCase(KEY_WORD_A)){
            throw new RuntimeException("keyword :关键字错误！"+keyword);
        }
        String mtable = sqlslice.get(M_TABLE);//表   必须   不加工
        if(mtable == null||"".equals(mtable)){
            throw new RuntimeException("mtable :要更新的表不能为空！"+mtable);
        }
        String dynconds = sqlslice.get(DYN_CONDS);//条件  过滤条件
        String ending = sqlslice.get(ENDING);//结束的部分
        ending =ending==null?"":ending;
        StringBuffer sb = new StringBuffer();
        dynconds = dynconds==null||dynconds==""?"":getInserts(dynconds,param);
        sb.append(keyword).append(" ").append(mtable).append(" ")
                .append(dynconds).append(" ").append(ending);
        return sb+"";
    }
    public static String[] getBatchSqls(Map<String,String> sqlslice, List<Map<String,Object>> param) {
        String keyword = sqlslice.get(KEY_WORD);//关键字  必须
        if(!keyword.equalsIgnoreCase(KEY_WORD_A)){
            throw new RuntimeException("keyword :关键字错误！"+keyword);
        }
        String mtable = sqlslice.get(M_TABLE);//表   必须   不加工
        if(mtable == null||"".equals(mtable)){
            throw new RuntimeException("mtable :要更新的表不能为空！"+mtable);
        }
        String dynconds = sqlslice.get(DYN_CONDS);//条件  过滤条件
        String ending = sqlslice.get(ENDING);//结束的部分
        ending =ending==null?"":ending;
        StringBuffer sb = new StringBuffer();
        dynconds = dynconds==null||dynconds==""?"":dynconds;
        sb.append(keyword).append(" ").append(mtable).append(" ");
        return batchInserts(sb.toString(),dynconds,param);
    }
    public static String getOrder(String sort,Map<String,Object> m){
        // String[] condArray = sort.split("#");
        String[] condArray = mySplit(sort,MINSPLIT);
        String sor= m.get(condArray[0])+"";
        if(sor==null||"".equals(sor)){
            return condArray[0];
        }
        return condArray[0]+" "+ sor;
    }
    //分页有三种可能  limit  ""  "index#page" limit 1,2  "index#OFFSET#page" limit 1 offset 2
    public static String getPaging(String paging,Map<String,Object> m){
        //String[] condArray = paging.split("#");
        String[] condArray = mySplit(paging,MINSPLIT);
        String pages=  condArray[0] ;
        if(pages==null||"".equals(pages.trim())){//第一个分页参数为空  就返回空的
            return "";
        }
        Object pagesize= m.get(PAGE_SIZE);
        if(pagesize==null||"".equals(pagesize)){// 没有传递分页值 也返回空
            return "";
        }
        int ps=(int) pagesize;
        Object pageindex=  m.get(PAGE_INDEX);
        if(pageindex==null || "".equals(pageindex)){
            return pages+" "+ps;
        }
        int pi =(int) pageindex;
        int mynum = (pi-1)* ps ;
        return pages+" "+ps+" "+condArray[1]+" "+ mynum;
    }

    public static String[] mySplit(String text,String separat){
        int off = 0;
        int len = 0;
        ArrayList<String> list = new ArrayList<>();
        int next =0;
        do{
            len ++;
            next = text.indexOf(separat,off);
            if(next==-1){
                list.add(text.substring(off));
                String[] result = new String[len];
                return  list.toArray(result);
            }
            list.add(text.substring(off, next));
            off = next + 1;
        }while(next!= -1);
        return null;
    }
    /**
     * @Desc
     * @param v   参数
     * @param spl      参数名称/参数值/值补充（/是否必填/字段类型/是否主键）
     * @param sqltyp   true 标识插入的sql  false标识非插入语句
     * @param skip      true 标识 按照sql参数字段不省略插入      false 标识v为空省略当前字段
     * @return
     */
    //是否必填/字段类型/是否主键     001  101  100
    public static String joint(Object v,String[] spl,Boolean sqltyp,Boolean skip){
        StringBuffer sb =new StringBuffer();
        String spl3=spl[3];
        //if (sqltyp && "1357".contains(spl3)) { //判断是否为主键   //是否必填/字段类型/是否主键   默认0为否1为是  001 011 110 111
        if (v == null || "".equals(v)) {//值 为空需要判断 是否为 必填项
            if (sqltyp && PRIFLAG.equals(spl3)) { //判断是否为主键   //是否必填/字段类型/是否主键   默认0为否1为是  001
                return sb.append("'").append(UUID.randomUUID()) +"'"; //添加主键
            }
            //if ("4567".contains(spl3) ) {//是否必填/字段类型/是否主键   默认0为否1为是   100  101 110 111
            if (NOTNULL.equals(spl3) || NOTNULLNUM.equals(spl3)) {//是否必填/字段类型/是否主键   默认0为否1为是   100
                throw new RuntimeException("参数‘" + spl[0] + "’是必填项！");
            }
            return skip?null:"";
        }
        //if ("2367".contains(spl3)) {//判断是否是数字 布尔   //是否必填/字段类型/是否主键   默认0为否1为是   010 011 110 111
        if (NUMFLAG.equals(spl3) || NOTNULLNUM.equals(spl3)) {//判断是否是数字 布尔   //是否必填/字段类型/是否主键   默认0为否1为是   010
            if (v instanceof Integer || v instanceof Boolean || v instanceof Float) {
                return  sqltyp?sb.append(v)+" ":sb.append(spl[1]).append(v)+" ";
            }
            throw new RuntimeException("sql字段‘" + Arrays.toString(spl) + "’类型定义错误！值为字符串型！");
        }
        if (v.toString().indexOf("or ",0) != -1) { //判断违法字符
            //if (v.toString().contains(" or ")) { //判断违法字符
            throw new RuntimeException("参数【" + v + "】存在违法字符 or！");
        }

        return sqltyp?sb.append("'").append(v)+"' ":sb.append(spl[1]).append(v).append(spl[2])+" ";
    }
    public static String getCond(String cond,Map<String,Object> m){
        if(cond==null || cond ==""){//空值过滤
            return "";
        }
        // String[] condArray = cond.split("@");
        String[] condArray = mySplit(cond,BIGSPLIT);
        StringBuffer sb = new StringBuffer();
        for(int i = 0; i < condArray.length; i++) {
            //  String[] condA = condArray[i].split("#");
            String[] condA = mySplit(condArray[i],MINSPLIT);
            if (condA.length == 1) {// 定值条件 a.a=b.b 或者 a.a='1'
                sb.append(condA[0]);
                continue;
            }
            if (condA.length < 4) {//  单个定值变量
                throw new RuntimeException("参数‘" + Arrays.toString(condA) + "’长度不够！");
            }
            Object v = m.get(condA[0]);
            String spll =joint(v,condA,false,false);
            sb.append(spll);
        }
        return sb+"";
    }
    public static String getOneInsert(String cond,Map<String,Object> m){
        // String[] condArray = cond.split("@");
        String[] condArray = mySplit(cond,BIGSPLIT);
        StringBuffer sbKey = new StringBuffer();
        StringBuffer sbVal = new StringBuffer();
        Boolean flag = true;// 第一个不加逗号
        for(int i = 0; i < condArray.length; i++){
            // String[] condA = condArray[i].split("#");
            String[] condA = mySplit(condArray[i],MINSPLIT);
            Object val= m.get(condA[0].trim());
            if (condA.length < 4) {//  单个定值变量
                throw new RuntimeException("参数‘" + condArray[i] + "’长度不够！");
            }
            String spll =joint(val,condA,true,false);
            if("".equals(spll)){
                continue;
            }
            if(flag){
                flag=false;
            }else{
                sbKey.append(",");
                sbVal.append(",");
            }
            sbVal.append(spll);
            sbKey.append(condA[1]).append(" ");
        }
        return "(" +sbKey+")  VALUES (" +sbVal +")";
    }

    public static String getInserts(String cond,List<Map<String,Object>> map) {
        // String[] condArray = cond.split("@");
        String[] condArray = mySplit(cond,BIGSPLIT);
        StringBuffer sbKey = new StringBuffer();
        StringBuffer sbVal = new StringBuffer();
        boolean flag = false;
        for (Map<String, Object> m : map) {
            boolean isFirst=true;
            if (flag) {// 非第一个 不需要再便历一遍数据字段
                sbVal.append(",(");
                for (int i = 0; i < condArray.length; i++) {
                    //String[] condA = condArray[i].split("#");
                    String[] condA = mySplit(condArray[i],MINSPLIT);
                    Object val = m.get(condA[0].trim());
                    //不是第一个字段 必须添加逗号
                    if (condA.length < 4) {//  单个定值变量
                        throw new RuntimeException("参数‘" + condArray[i] + "’长度不够！");
                    }
                    String spll =joint(val,condA,true,true);
                    if("".equals(spll)){
                        continue;
                    }
                    if(isFirst){
                        isFirst=false;
                    }else{
                        sbVal.append(",");
                    }
                    sbVal.append(spll);
                }
                sbVal.append(")");
                continue;
            }
            //第一次遍历需要 insert into (id,name,birth,gae,) values ('101','小米','2021-01-01',1);
            sbVal.append("(");
            sbKey.append("(");
            flag = true;
            for (int i = 0; i < condArray.length; i++) {
                //String[] condA = condArray[i].split("#");
                String[] condA = mySplit(condArray[i],MINSPLIT);
                Object val = m.get(condA[0].trim());
                if (condA.length < 4) {//  单个定值变量
                    throw new RuntimeException("参数‘" + condArray[i] + "’长度不够！");
                }
                String spll =joint(val,condA,true,true);
                if("".equals(spll)){
                    continue;
                }
                if(isFirst){
                    isFirst=false;
                }else{
                    sbKey.append(",");
                    sbVal.append(",");
                }
                sbVal.append(spll);
                sbKey.append(condA[1]).append(" ");
            }
            sbVal.append(")");
            sbKey.append(")");
        }
        return sbKey + " VALUES " + sbVal + ";";
    }
    @SuppressWarnings({ "rawtypes", "unchecked"})
    public static String[] batchInserts(String str ,String cond,List<Map<String,Object>> map){
        ArrayList al =new ArrayList();
        int num =0;
        List<Map<String,Object>> newmap= new ArrayList<>() ;
        for (Map<String,Object> m :map){
            num++;
            newmap.add(m);
            if(num % 2==0){
                al.add(str+getInserts(cond, newmap));
                newmap.clear();;
            }
        }
        al.add(str+getInserts(cond, newmap));
        String s[] = new String[al.size()];
        return  (String[]) al.toArray(s);
    }
}

